<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use App\Models\Examinfo;
use App\Models\Grade;
use App\Models\Student;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Http\Request;
use function SebastianBergmann\Type\TestFixture\callback_function;

/**
 * Class GradeController
 * @package App\Http\Controllers\Admin
 * User: ZengMingYong
 * Date: 2020/7/3
 * Time: 11:02
 * 成绩控制器
 */
class GradeController extends CommonController
{
//列表
    public function index()
    {

        //  dd(Grade::find(1)->student);
        $data = Grade::orderBy('id', 'desc')->paginate($this->page);
        return view('admin.grade.index', compact('data'));
    }

    /**
     * 添加视图
     *
     */
    public function create()
    {
        //获取考试信息表的考试名称
        $examinfo_name = Examinfo::pluck('test_name')->toArray();
        return view('admin.grade.create', compact('examinfo_name'));
    }

    /**
     * @param Request $request
     * @return array|\Illuminate\Http\RedirectResponse|\Illuminate\Routing\Redirector
     * @throws \Illuminate\Validation\ValidationException
     */
    public function store(Request $request)
    {
        //ajx
        if ($request->ajax() && $request->input('test_name')) {
            if (empty($request->input('class_name'))) {
                $class_name = Examinfo::where('test_name', $request->input('test_name'))
                    ->pluck('class_name')->toArray();
                return ['status' => 0, 'msg' => '成功', 'class_name' => $class_name];
            }
            $subject = Examinfo::where([
                ['test_name', $request->input('test_name')]
                , ['class_name', $request->input('class_name')]
            ])->pluck('subject')->toArray();
            return ['status' => 0, 'msg' => '成功', 'subject' => $subject];
        }
        //POST
        $this->validate($request, [
            "test_name" => "required",
            "class_name" => "required",
            "subject" => "required",
            "student_code" => "required",
            "name" => "required",
            "score" => "required",
        ]);
        //验证学生是否存在
        $student = Student::where([
            ['student_code', $request->get('student_code')],
            ['name', $request->get('name')]
        ])->first();
        if ($student == null) {
            return redirect(route('admin.grade.create'))->with(['error' => '学生账号不存在']);
        }
        //获取examinfo的id
        $examifo = Examinfo::where([
            ['test_name', $request->get('test_name')],
            ['class_name', $request->get('class_name')],
            ['subject', $request->get('subject')]
        ])->first();
        if ($examifo == null) {
            return redirect(route('admin.grade.create'))->with(['error' => '考试信息不存在']);
        }
        //获取students_id和examifos_id
        //入库create
        Grade::create(
            [
                'students_id' => $student->student_code,
                'examinfos_id' => $examifo->id,
                'score' => $request->input('score')
            ]
        );
        return redirect(route('admin.grade.index'))->with(['error' => '添加成功']);
    }

    public function show(Grade $grade)
    {

    }

    public function edit(Grade $grade)
    {
        return view('admin.grade.edit', compact('grade'));
    }

    /**
     * @param Request $request
     * @param Grade $grade
     * @return \Illuminate\Http\RedirectResponse|\Illuminate\Routing\Redirector
     * @throws \Illuminate\Validation\ValidationException
     */
    public function update(Request $request, Grade $grade)
    {

        $post = $this->validate($request, [
            'score' => 'required',
        ]);
        $grade->update($post);
        return redirect(route('admin.grade.index'))->with(['error' => '斜杠成功']);
    }

    /**
     * @param Grade $grade
     * @return array
     * @throws \Exception
     * 删除
     */
    public function destroy(Grade $grade)
    {
        $grade->delete();
        return ['error' => 0, 'msg' => '删除成功'];
    }

    //导入成绩
    public function lead(Request $request)
    {
        //获取考试名称
        $examinfos = Examinfo::pluck('test_name')->toArray();
        //去重
        $examinfo = array_unique($examinfos);
        //考试信息 二级联动请求
        if ($request->ajax() && $request->input('test_name')) {
            //判断是否为空
            if (empty($request->input('class_name'))) {
                //获取班级
                $class_name = Examinfo::where('test_name', $request->input('test_name'))
                    ->pluck('class_name')->toArray();
                //json格式返回Ajax的请求
                return ['status' => 0, 'msg' => '成功', 'class_name' => $class_name];
            }
        }
        //判断请求类型且文件是否存在且文件是否有效
        if ($request->isMethod('POST') && $request->file('file') && $request->file('file')->isValid()) {
            //文件上传
            $file = $request->file('file');
            //允许上传的格式
            $allowed_extensions = ["xlsx", 'xls', 'csv'];
            //判断是否是允许上传的类型
            if (!in_array($file->getClientOriginalExtension(), $allowed_extensions)) {
                //如果不是返回提示
                return redirect(route('admin.grade.lead'))->with(['error' => '文件格式不正确']);
            } else {
                //数据验证
                $post = $this->validate($request, [
                    'test_name' => 'required',
                    'class_name' => 'required',
                    'class_teacher' => 'required',
                ]);
                //获取科目名
                $query= Examinfo::where($post);
                $bool = $query->pluck('subject')->toArray();
                //如果没有搜索到，说明不存在
                if (empty($bool)) {
                    //返回提示信息
                    return redirect(route('admin.grade.lead'))->with(['error' => '班主任名字错误']);
                }
                //文件上传的路径
                $grade_excel = $request->file('file')->store('', 'grade');
                //文件的绝对路径
                $inputFileName = public_path('/uploads/grade/') . $grade_excel;
                //读取excel
                $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);
                //返回数组类型
                $worksheet = $spreadsheet->getActiveSheet()->toArray(null, true, false, false);
                //获取第一列并且删除原数组的第一列
                $index = array_shift($worksheet);
                //获取科目
                $subjects = array_slice($index, 2);
                //去空
                $subject = array_filter($subjects);

                //判断是否有成绩
                if (empty($subject)){
                    //返回提示
                    return redirect(route('admin.grade.lead'))->with(['error' => '成绩不存在']);
                }
                //验证excel是否有非法的科目或者不存在的科目
                foreach ($subject as $key => $value) {
                    if (!in_array($value, $bool, true)) {
                        //如果有多余的参数则返回提示信息
                        return redirect(route('admin.grade.lead'))->with(['error' => '成绩表格名字错误']);
                    }
                }
                $arrays=[];
                //成绩数量
                $keys=0;
                //循环格式添加成绩
                foreach ($subject as $item=>$vo) {
                    //考试信息id
                    $examinfos_id=Examinfo::where($post)->where('subject',$subject[$item])->value('id');;
                    //科目
                    foreach ($worksheet as $ke => $valu) {
                        //去空 获取成绩
                        $siz = array_slice(array_filter($valu), 1);
                        //获取学号
                        $arrays[$keys]['students_id']=$siz[0];
                        //获取成绩
                        $arrays[$keys]['score']=$siz[$item+1];
                        //考试信息id
                        $arrays[$keys]['examinfos_id']=$examinfos_id;
                        $keys++;
                    }
                }
               $dd= Grade::insert($arrays);//addAll
                return redirect(route('admin.grade.index'))->with(['error'=>'导入成功']);
            }
        }
        //返回视图
        return view('admin.grade.lead', compact('examinfo'));
    }
    //导出成绩


    public function export(Request $request){

        /**
         * 关联查询
         * whereHas 查询关联数据库
         * 参数1 关联名称
         *  参数2 匿名函数
         *      **匿名函数参数
         *      *** Illuminate\Database\Eloquent\Builder;
         *          函数引入外部类 function function_name() use 外部类路径 { }
         */
        $querys = Grade::whereHas('examinfo', function (Builder $query) use ($request) {
            //where 针对 examinfo 查询
        $query->where([
                ['test_name',$request->input('test_name')],
                ['class_name',$request->input('class_name')]
            ]);
        })->orderBy('students_id','asc');

        $data=$querys->get();
        if ($request->isMethod('post') && !($data->isEmpty())){




            //获取考试信息集合
            $examinfo=[];
            foreach ($data as $k=> $datum){
                $examinfo[$k]['students_id']=$datum['students_id'];
                $examinfo[$k]['name']=$datum->student->name;
                $examinfo[$k]['subject']=$datum->examinfo->subject;
                $examinfo[$k]['score']=$datum->score;
            }
            $title = ['学号','姓名'];
            $subject=[];
            foreach ($examinfo as $k=>$v){
                $subject[$k]=$v['subject'];
            }
            $subject=array_unique($subject);//去重,获取科目集合
            $title=array_merge($title,$subject);//拼接标题
           $reset=array();
            foreach ($examinfo as $ks=>$vs){
                $reset[$ks]['students_id']=$vs['students_id'];
                $reset[$ks]['name']=$vs['name'];
                $reset[$ks][(array_keys($title,$vs['subject'])[0])]=$vs['score'];
            }

            $re=[];
            $i=0;
            foreach ($reset as $ky=>$vy){
                $keys=array_keys($vy);
                if (empty($re)){
                    $re[$i][0]=$vy['students_id'];
                    $re[$i][1]=$vy['name'];
                    $re[$i][$keys[2]]=$vy[$keys[2]];
                }elseif ($re[$i][0]==$vy['students_id']){
                    $re[$i][$keys[2]]=$vy[$keys[2]];
                }else{
                    $i++;
                    $re[$i][0]=$vy['students_id'];
                    $re[$i][1]=$vy['name'];
                    $re[$i][$keys[2]]=$vy[$keys[2]];
                }
            }
            $this->array_sort($re,0);
            $sorts=[];
            foreach ($re as $item){
                ksort($item );
              $sorts[]=$item;
            }
            // Create new Spreadsheet object
            $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();

            // 方法一，使用 setCellValueByColumnAndRow
            //表头
            //设置单元格内容
            foreach ($title as $key => $value) {
                // 单元格内容写入
                $sheet->setCellValueByColumnAndRow($key + 1, 1, $value);
            }
            $row = 2; // 从第二行开始
            foreach ($sorts as $item) {
                $column = 1;
                foreach ($item as $value) {
                    // 单元格内容写入
                    $sheet->setCellValueByColumnAndRow($column, $row, $value);
                    $column++;
                }
                $row++;
            }

            $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
            // Redirect output to a client’s web browser (Xlsx)
              header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
              header('Content-Disposition: attachment;filename="01simple.xlsx"');
              header('Cache-Control: max-age=0');
              // If you're serving to IE 9, then the following may be needed
              header('Cache-Control: max-age=1');

              // If you're serving to IE over SSL, then the following may be needed
              header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
              header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
              header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
              header('Pragma: public'); // HTTP/1.0
            $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
             $writer->save('php://output');
           // $writer->save("uploads/excel/".time().".xlsx");

            exit;
        }

        return view('admin.grade.export', compact('data'));
    }
    //二维数组排序
   private function array_sort($arr,$keys,$type='asc'){
        $keysvalue = $new_array = array();
        foreach ($arr as $k=>$v){
            $keysvalue[$k] = $v[$keys];
        }
        if($type == 'asc'){
            asort($keysvalue);
        }else{
            arsort($keysvalue);
        }
        reset($keysvalue);
        foreach ($keysvalue as $k=>$v){
            $new_array[$k] = $arr[$k];
        }
        return $new_array;
    }

}
